
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ----------------------------
-- 方案管新需求数据库脚本
-- ----------------------------

DROP INDEX `index_hot_keyword_name_unique` ON `hot_keyword`;
DROP INDEX `index_solution_id_unique` ON `solution_rank`;
DROP INDEX `index_page_code_unique` ON `ad_page`;
DROP INDEX `index_area_code_unique` ON `ad_area`;
DROP INDEX `index_position_code_unique` ON `ad_position`;
DROP INDEX `index_user_action_statis_bussiness_unique` ON `user_action_statis`;
DROP INDEX `index_ad_number_unique` ON `ad_serving`;

DROP TABLE `hot_keyword`;
DROP TABLE `ad_resource`;
DROP TABLE `solution_rank`;
DROP TABLE `ad_page`;
DROP TABLE `ad_area`;
DROP TABLE `ad_position`;
DROP TABLE `user_action_statis`;
DROP TABLE `ad_serving`;
DROP TABLE `ad_serving_user_group`;

CREATE TABLE `hot_keyword` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `name` varchar(20) NOT NULL COMMENT '关键词名称',
  `highlight` int(1) NULL DEFAULT 0 COMMENT '是否高亮，0：否，1：是',
  `sort` int(11) NOT NULL DEFAULT 0 COMMENT '排序',
  `is_del` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除， 0：否，1：是',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` bigint(50) NOT NULL COMMENT '创建用户标识',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近修改时间',
  `modified_by` bigint(50) NOT NULL COMMENT '最近修改用户标识',
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `index_hot_keyword_name_unique` (`name` ASC) USING BTREE
)
  COMMENT = '热搜关键词';

CREATE TABLE `ad_resource` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `ad_title` varchar(50) NOT NULL COMMENT '广告标题',
  `ad_url` varchar(255) NOT NULL COMMENT '广告链接',
  `resource_type` int(11) NOT NULL DEFAULT 1 COMMENT '资源类型，0：文本，1：图片，2：视频',
  `resource_url` varchar(255) NULL COMMENT '广告资源（图片、视频）URL地址',
  `text` varchar(255) NULL COMMENT '广告文本信息',
  `is_del` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除， 0：否，1：是',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `created_by` bigint(50) NOT NULL COMMENT '添加用户',
  `modified_by` bigint(50) NOT NULL COMMENT '更新用户',
  PRIMARY KEY (`id`)
)
  COMMENT = '广告素材';

CREATE TABLE `solution_rank` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `solution_id` bigint(11) NOT NULL COMMENT '方案 ID',
  `rank_type` int(1) NOT NULL COMMENT '排行类型：1： 热搜，2：精品',
  `is_del` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除， 0：否，1：是',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` bigint(50) NOT NULL COMMENT '创建用户标识',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近修改时间',
  `modified_by` bigint(50) NOT NULL COMMENT '最近修改用户标识',
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `index_solution_id_unique` (`solution_id` ASC, `rank_type` ASC) USING BTREE
)
  COMMENT = '方案排行榜，热搜列表按修改时间倒序排列，取最新 N 条数据。';

CREATE TABLE `ad_page` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `page_code` varchar(50) NOT NULL COMMENT '页面唯一编码',
  `name` varchar(50) NOT NULL COMMENT '页面名称',
  `is_del` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除， 0：否，1：是',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近修改时间',
  `created_by` bigint(50) NOT NULL COMMENT '创建用户',
  `modified_by` bigint(50) NOT NULL COMMENT '修改用户',
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `index_page_code_unique` (`page_code` ASC) USING BTREE
)
  COMMENT = '广告页';

CREATE TABLE `ad_area` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `area_code` varchar(50) NOT NULL COMMENT '区域唯一代码',
  `name` varchar(50) NOT NULL COMMENT '区域名称',
  `page_code` varchar(50) NOT NULL COMMENT '所属页面代码',
  `is_del` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除， 0：否，1：是',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `created_by` bigint(50) NOT NULL COMMENT '创建用户',
  `modified_by` bigint(50) NOT NULL COMMENT '修改用户',
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `index_area_code_unique` (`area_code` ASC) USING BTREE
)
  COMMENT = '广告区域';

CREATE TABLE `ad_position` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `position_code` varchar(50) NOT NULL COMMENT '广告位唯一代码',
  `name` varchar(50) NOT NULL COMMENT '广告位名称',
  `area_code` varchar(50) NOT NULL COMMENT '所属区域代码',
  `description` varchar(100) NOT NULL COMMENT '说明信息',
  `position_type` int(11) NOT NULL DEFAULT 1 COMMENT '广告位类型，0：文本，1：图片，2：视频',
  `is_del` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除， 0：否，1：是',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `created_by` bigint(50) NOT NULL COMMENT '创建用户',
  `modified_by` bigint(50) NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `index_position_code_unique` (`position_code` ASC) USING BTREE
)
  COMMENT = '广告位';

CREATE TABLE `user_action_statis` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `business_type` varchar(50) NOT NULL COMMENT '业务类型',
  `business_id` bigint(11) NOT NULL COMMENT '业务标识',
  `statis_date` date NOT NULL COMMENT '统计日期',
  `text` varchar(50) NULL COMMENT '选项名',
  `log_count` int(255) NOT NULL DEFAULT 0 COMMENT '统计值',
  `effect_time` datetime NULL COMMENT '生效时间',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `index_user_action_statis_bussiness_unique` (`business_type` ASC, `business_id` ASC, `statis_date` ASC) USING BTREE
)
  COMMENT = '用户日志统计';

CREATE TABLE `ad_serving` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `ad_number` varchar(50) NOT NULL COMMENT '广告编号',
  `status` int(11) NOT NULL DEFAULT 0 COMMENT '状态，0：已下架，1：上架中',
  `shelve_time` datetime NULL COMMENT '上架时间',
  `shelve_days` int(11) NULL COMMENT '上架周期（天）',
  `resource_id` bigint(11) NOT NULL COMMENT '广告资源',
  `position_code` varchar(50) NOT NULL COMMENT '投放广告位',
  `ad_type` int(11) NOT NULL DEFAULT 0 COMMENT '广告类型，0：默认广告，1：投放广告',
  `is_del` int(11) NOT NULL DEFAULT 0 COMMENT '是否删除， 0：否，1：是',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `created_by` bigint(50) NOT NULL COMMENT '创建用户',
  `modified_by` bigint(50) NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `index_ad_number_unique` (`ad_number` ASC) USING BTREE
)
  COMMENT = '广告投放';

CREATE TABLE `ad_serving_user_group` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `ad_serving_id` bigint(11) NOT NULL COMMENT '投放广告标识',
  `user_group_id` int(11) NOT NULL COMMENT '用户组的 Value',
  PRIMARY KEY (`id`)
)
  COMMENT = '广告投放用户群';

ALTER TABLE brand_owner ADD `sort` int(11) NOT NULL DEFAULT 0 COMMENT '排序';
ALTER TABLE common_type_data ADD `highlight` int(1) NULL DEFAULT 0 COMMENT '是否高亮，0：否，1：是';
ALTER TABLE common_type_data ADD `created_by` int(11) NULL COMMENT '创建用户';
ALTER TABLE common_type_data ADD `modified_by` int(11) NULL COMMENT '修改用户';
ALTER TABLE brand_owner ADD `modified_by` int(11) NULL COMMENT '修改用户';
ALTER TABLE brand_owner ADD `operator` varchar(50) NULL COMMENT '操作人';
ALTER TABLE solution_rank ADD `operator` varchar(50) NULL COMMENT '操作人';
ALTER TABLE hot_keyword ADD `operator` varchar(50) NULL COMMENT '操作人';
ALTER TABLE common_type_data ADD `operator` varchar(50) NULL COMMENT '操作人';
ALTER TABLE ad_serving ADD `operator` varchar(50) NULL COMMENT '操作人';
ALTER TABLE user_action_log ADD `position_code` varchar(50) NULL COMMENT '页面位置码';

ALTER TABLE user_action_log ADD INDEX `index_user_action_log_business_type_id_code` (`business_type`,`business_id`,`position_code`) USING BTREE;


-- ----------------------------
-- 广告位初始化数据
-- ----------------------------

BEGIN;
INSERT INTO `ad_page` VALUES (1, 'my_treasure', '我的财富', 0, '2018-05-17 13:29:56', '2018-05-17 13:29:56', 123, 123);
INSERT INTO `ad_page` VALUES (2, 'solution', '方案管', 0, '2018-05-17 13:29:56', '2018-05-21 11:17:19', 123, 123);
COMMIT;

BEGIN;
INSERT INTO `ad_area` VALUES (1, 'my_treasure_top', '我的财富TOP广告区域', 'my_treasure', 0, '2018-05-15 17:42:25', '2018-05-17 13:39:21', 123, 123);
INSERT INTO `ad_area` VALUES (3, 'solution_down', '方案管底部广告区域', 'solution', 0, '2018-05-15 17:42:25', '2018-06-07 09:56:04', 123, 123);
INSERT INTO `ad_area` VALUES (4, 'solution_top', '方案管TOP广告区域', 'solution', 0, '2018-05-15 17:42:25', '2018-05-21 11:20:41', 123, 123);
COMMIT;

BEGIN;
INSERT INTO `ad_position`(`id`, `position_code`, `name`, `area_code`, `description`, `position_type`, `is_del`, `created_time`, `modified_time`, `created_by`, `modified_by`) VALUES (1, 'my_treasure_top_1', '我的财富广告位', 'my_treasure_top', '我的财富顶部广告位', 1, 0, '2018-05-16 13:35:19', '2018-06-12 11:53:45', 123, 123);
INSERT INTO `ad_position`(`id`, `position_code`, `name`, `area_code`, `description`, `position_type`, `is_del`, `created_time`, `modified_time`, `created_by`, `modified_by`) VALUES (5, 'solution_down_1', '找方案广告位1', 'solution_down', '方案管顶部广告位', 1, 0, '2018-05-16 13:35:19', '2018-06-12 11:53:19', 123, 123);
INSERT INTO `ad_position`(`id`, `position_code`, `name`, `area_code`, `description`, `position_type`, `is_del`, `created_time`, `modified_time`, `created_by`, `modified_by`) VALUES (6, 'solution_down_2', '找方案广告位2', 'solution_down', '方案管顶部广告位', 1, 0, '2018-05-16 13:35:19', '2018-06-12 11:53:21', 123, 123);
INSERT INTO `ad_position`(`id`, `position_code`, `name`, `area_code`, `description`, `position_type`, `is_del`, `created_time`, `modified_time`, `created_by`, `modified_by`) VALUES (7, 'solution_down_3', '找方案广告位3', 'solution_down', '方案管顶部广告位', 1, 0, '2018-05-16 13:35:19', '2018-06-12 11:53:24', 123, 123);
INSERT INTO `ad_position`(`id`, `position_code`, `name`, `area_code`, `description`, `position_type`, `is_del`, `created_time`, `modified_time`, `created_by`, `modified_by`) VALUES (8, 'solution_down_4', '找方案广告位4', 'solution_down', '方案管顶部广告位', 1, 0, '2018-05-16 13:35:19', '2018-06-12 11:53:27', 123, 123);
INSERT INTO `ad_position`(`id`, `position_code`, `name`, `area_code`, `description`, `position_type`, `is_del`, `created_time`, `modified_time`, `created_by`, `modified_by`) VALUES (9, 'solution_down_5', '找方案广告位5', 'solution_down', '方案管顶部广告位', 1, 0, '2018-05-16 13:35:19', '2018-06-12 11:53:31', 123, 123);
INSERT INTO `ad_position`(`id`, `position_code`, `name`, `area_code`, `description`, `position_type`, `is_del`, `created_time`, `modified_time`, `created_by`, `modified_by`) VALUES (10, 'solution_down_6', '找方案广告位6', 'solution_down', '方案管顶部广告位', 1, 0, '2018-05-16 13:35:19', '2018-06-12 11:53:34', 123, 123);
INSERT INTO `ad_position`(`id`, `position_code`, `name`, `area_code`, `description`, `position_type`, `is_del`, `created_time`, `modified_time`, `created_by`, `modified_by`) VALUES (11, 'solution_down_7', '找方案广告位7', 'solution_down', '方案管顶部广告位', 1, 0, '2018-05-16 13:35:19', '2018-06-12 11:53:38', 123, 123);
COMMIT;


-- ----------------------------
-- 迁移旧的热词表到新热词表
-- ----------------------------

BEGIN;
insert into hot_keyword(id, name, highlight, sort, is_del, created_time, created_by, modified_time, modified_by, operator)
  select id, hot_word, is_highlight, sort, is_delete, create_time, 5056816814496224131, last_modified_time, 5056816814496224131, create_user_name from column_hotword
on DUPLICATE KEY UPDATE modified_time = VALUES(modified_time);

drop TABLE column_hotword;
COMMIT;

-- ----------------------------
-- 去重重复的栏目标签
-- ----------------------------

BEGIN;
UPDATE column_tag SET STATUS=1 WHERE NAME IN (SELECT a.NAME FROM (SELECT NAME FROM column_tag GROUP BY NAME HAVING count(NAME)> 1) a);
COMMIT;

BEGIN;
UPDATE column_tag SET STATUS=0 WHERE id IN (SELECT a.id FROM (SELECT id FROM column_tag GROUP BY NAME HAVING count(NAME)> 1) a);
COMMIT;

BEGIN;
UPDATE special_column c INNER JOIN (SELECT id,tag_id,(SELECT id FROM column_tag WHERE NAME=(SELECT NAME FROM column_tag WHERE id=c.tag_id) AND STATUS=0) new_tag_id
FROM (SELECT id,tag_id FROM special_column WHERE tag_id IN (SELECT id FROM column_tag WHERE NAME IN (SELECT NAME FROM column_tag GROUP BY NAME HAVING count(NAME)> 1))
AND tag_id IN (SELECT id FROM column_tag WHERE STATUS=1)) AS c) t ON c.id=t.id SET c.tag_id=t.new_tag_id;
COMMIT;


-- ----------------------------
-- 品牌主与用户基础表中的用户名同步
-- ----------------------------

BEGIN;
UPDATE user_profile up SET user_name  = (SELECT (user_name) FROM user_basic WHERE  user_id = up.user_id)
where up.user_id in (select user_id from user_basic);
COMMIT;

BEGIN;
UPDATE brand_firm bf SET user_name  = (SELECT (user_name) FROM user_basic WHERE user_id = bf.user_id)
WHERE bf.user_id in (select user_id from user_basic);
COMMIT;


-- ----------------------------
-- 修改用户基础表的必填字段
-- ----------------------------

ALTER TABLE user_basic MODIFY `nick_name` varchar(50) NULL;
ALTER TABLE user_basic MODIFY `gender` varchar(10) NULL;


-- ----------------------------
-- 补充用户表的数据（之前在 brand_firm 表，但不在用户表中的）
-- ----------------------------

insert into user_basic(user_id, user_name, nick_name, user_avatar, mobile, email)
  select user_id, user_name, user_name, firm_icon, contact_mobile, contact_email from brand_firm
  where user_id not in (select user_id from user_basic) and user_name is not null
on DUPLICATE KEY UPDATE user_name = VALUES(user_name);

insert into user_profile(user_id, user_name, icon, role, is_del)
select user_id, user_name, firm_icon, 2, 0 from brand_firm
where user_id not in (select user_id from user_profile) and user_name is not null
on DUPLICATE KEY UPDATE user_name = VALUES(user_name);




SET FOREIGN_KEY_CHECKS=1;